package cn.cjy.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.cjy.dao.ArticleDao;
import cn.cjy.model.Article;
import cn.cjy.utils.DBUtil;
import cn.cjy.utils.MysqlUtil;

public class ArticleDaoImpl implements ArticleDao{

	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	List<Article> ar = null;
	int n = 0;
	@Override
	public List<Article> all() {
		List<Article> articles=new ArrayList<Article>();
		try {
			String sql="select * from article where 1=1";
			ResultSet rs=MysqlUtil.excuteMysqlSql(sql);
			Article article=null;
			while(rs.next()) {
				article=new Article();
				article.setId(rs.getInt("id"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));
				article.setCatalogs(rs.getString("catalogs"));
				article.setCover(rs.getString("cover"));
				article.setUserinfo(rs.getString("userinfo"));
				article.setPosttime(rs.getDate("posttime"));
				article.setReview(rs.getString("review"));
				articles.add(article);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return articles;
	}

	@Override
	public Article one(String id) {
		
		
		Article article=null;
		try {
			conn = DBUtil.getConnection();
			String sql = "select * from article where id=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,id);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				article=new Article();
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));;
				article.setCatalogs(rs.getString("catalogs"));
				article.setCover(rs.getString("cover"));
				article.setUserinfo(rs.getString("userinfo"));
				article.setPosttime(rs.getDate("posttime"));
				article.setReview(rs.getString("review"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return article;
	}

	@Override
	public Integer update(Article article) {
		try {
			conn = DBUtil.getConnection();
			String sql = "update article set title=?,content=?,catalogs=?,cover=?,userinfo=?,posttime=?,review=? where title=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(8,article.getTitle());	
			pstmt.setString(1,article.getTitle());		
			pstmt.setString(2,article.getContent());
			pstmt.setString(3,article.getCatalogs());
			pstmt.setString(4,article.getCover());
			pstmt.setString(5,article.getUserinfo());
			pstmt.setDate(6, new Date(article.getPosttime().getTime()));
			pstmt.setString(7,article.getReview());

			n = pstmt.executeUpdate();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("n=" + n);
		return n;
	}

	@Override
	public Integer add(Article article) {
		String sql = " insert into article (title,content,catalogs,cover,userinfo,posttime,review) values ('" + article.getTitle() + "','" + article.getContent() + "','"+article.getCatalogs()+"','"+article.getCover()+"','"+article.getUserinfo()+"','"+new Date(article.getPosttime().getTime())+"','"+article.getReview()+"')";

		Integer row = MysqlUtil.excuteMysqlUpdate(sql);

		return row;
	}

	@Override
	public Integer delete(String id) {
		// TODO Auto-generated method stub
		String sql="delete from article where id="+id;
		Integer row = MysqlUtil.excuteMysqlUpdate(sql);

		return row;
	}

	@Override
	public List<Article> selectArticleByTitle(String key1) {
		List<Article> ar1=new ArrayList<Article>();
		try {
			conn = DBUtil.getConnection();
			String sql = "select * from article where title like ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,"%".concat(key1).concat("%"));
			rs = pstmt.executeQuery();
			Article article=null;
			while(rs.next()) {
				article=new Article();
				article.setId(rs.getInt("id"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));;
				article.setCatalogs(rs.getString("catalogs"));
				article.setCover(rs.getString("cover"));
				article.setUserinfo(rs.getString("userinfo"));
				article.setPosttime(rs.getDate("posttime"));
				article.setReview(rs.getString("review"));
				ar1.add(article);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ar1;
	}

	@Override
	public List<Article> selectArticleByUserinfo(String key2) {
		try {
			conn = DBUtil.getConnection();
			String sql = "select * from article where userinfo like ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,"%".concat(key2).concat("%"));
			rs = pstmt.executeQuery();
			ar=new ArrayList<Article>();
			while(rs.next()) {
				Article article=new Article();
				article.setId(rs.getInt("id"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));;
				article.setCatalogs(rs.getString("catalogs"));
				article.setCover(rs.getString("cover"));
				article.setUserinfo(rs.getString("userinfo"));
				article.setPosttime(rs.getDate("posttime"));
				article.setReview(rs.getString("review"));
				ar.add(article);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ar;
	}

}
